package com.neuedu.jdbc;

import com.neuedu.jdbc.util.DBUtils;

import java.sql.*;


/**
 * @author 金山
 * 项目：jdbc
 * site: https://blog.fulfill.com.cn
 * 描述  练习CRUD代码
 * @data 2024/11/89:37
 */
public class CRUDTest {


    public static void main(String[] args) {

        CRUDTest test=  new CRUDTest();
        test.select();
        // test.insert();
        // test.update();
        // test.delete();


    }


    /**
     * 查询    【需要解析结果集 rs】
     *     查询集合          selectAll
     *     根据住建查询       selectById
     * 更新    影响的行数  int
     *      根据主键更新
     *      根据条件更新
     *
     * 插入   影响的行数  int
     *      单条插入
     *      批量插入
     *  删除 影响的行数  int
     *      根据主键删除
     *      根据条件删除
     */


    /**
     * 查询
     */
    public  void  select(){

        Connection conn = null;
        Statement stmt = null;
        ResultSet  rs = null;
        try {
            conn = DBUtils.getConnection();

            //3 创建statement
            stmt = conn.createStatement();

            //4 执行sql语句  结果集 rs
            String sql = "SELECT   `empno`, `ename`, `job`,  `mgr`, `hiredate`, `sal`, `comm`, `deptno` FROM `emp` ";
            rs = stmt.executeQuery(sql);

            //5 解析 结果集
            while(rs.next()){ //第一行
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                int mgr = rs.getInt("mgr");
                Date hiredate = rs.getDate("hiredate");
                double sal = rs.getDouble("sal");
                double comm = rs.getDouble("comm");
                int deptno = rs.getInt("deptno");

                System.out.printf(" empno:%d \tename:%s\tjob:%s\tmgr:%d\thiredate:%s\tsal:%f\tcomm:%f\tdeptno:%d  \r\n",
                        empno,
                        ename,
                        job,
                        mgr,
                        hiredate,
                        sal,
                        comm,
                        deptno


                        );


            }





        } catch ( SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //   6 关闭资源
           DBUtils.close(conn,stmt,rs);
        }





    }

    public  void  insert(){

        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DBUtils.getConnection();

            //3 创建statement
            stmt = conn.createStatement();

            //4 执行sql语句  结果集 rs
            StringBuffer sql  = new StringBuffer();
            sql.append("INSERT INTO `emp` (  `empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`,  `deptno`) " );
            sql.append("VALUES ( " );
            sql.append("   2001, 'JDBC','job',7369,'2024-11-07',9999,null,10   " );
            sql.append(") " );

            int count = stmt.executeUpdate(sql.toString());

            System.out.println("插入影响的行数 count = " + count);

        } catch ( SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //   6 关闭资源
            DBUtils.close(conn,stmt,null);
        }





    }




    public  void  update(){

        Connection conn = null;
        Statement stmt = null;
        ResultSet  rs = null;
        try {
            conn = DBUtils.getConnection();

            //3 创建statement
            stmt = conn.createStatement();

            //4 执行sql语句  结果集 rs
            StringBuffer sql  = new StringBuffer();
            sql.append("UPDATE emp SET ename = 'update' , job = 'MANAGER'  WHERE empno = 2001 " );

            int count = stmt.executeUpdate(sql.toString());

            System.out.println("更新影响的行数 count = " + count);

        } catch ( SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //   6 关闭资源
            //   6 关闭资源
            DBUtils.close(conn,stmt,null);
        }





    }


    public  void  delete(){

        Connection conn = null;
        Statement stmt = null;
        ResultSet  rs = null;
        try {
            conn = DBUtils.getConnection();

            //3 创建statement
            stmt = conn.createStatement();

            //4 执行sql语句  结果集 rs
            StringBuffer sql  = new StringBuffer();
            sql.append("delete  from emp  WHERE empno = 2001 " );

            int count = stmt.executeUpdate(sql.toString());

            System.out.println("删除影响的行数 count = " + count);

        } catch ( SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //   6 关闭资源
            //   6 关闭资源
            DBUtils.close(conn,stmt,null);
        }





    }



}
